Sumo Logic コンソールへの非ログイン日数が90日を超えるユーザを検出してアラートを発報する方法

最初にできないことだけ以下に記載します。

• ユーザ名を抽出することは出来ません。代わりにユーザのメールアドレスを抽出できます。
• 非ログインユーザを一覧で取得することは出来ません。ですが、Audit のログでアクティビティを追跡することは出来ます。

力技にはなりますが、以下でご紹介する方法を使って、90 日以上ログインしていないユーザ情報を抽出することが出来ます。

90日以上ログインしていないユーザの抽出

Audit ログに記録されるアクティビティイベントを拾うクエリを作成します。ログを使うため、前提として "１回はログインしていることが必要である" のと、"ログ保管期間内であること" を考慮いただく必要があります。

早速ですが、以下のクエリで 90日以上ログインしていないユーザのメールアドレスと「最終ログイン」「現在時刻」「非ログイン日数」を抽出することが出来ます。（クエリ文の解説は各行のコメントアウトをご確認ください。）

// sumologic_audit（監査ログ）に対して、login のアクションが success しているログを検索
_index=sumologic_audit action=login status=SUCCESS

// sumologic_audit の sourceCategory として user_activity のログにフィルタリング
| where _sourceCategory="user_activity"

// withtime <時間情報を持つフィールド> でミリ秒単位のタイムスタンプを含む JSON オブジェクトを作成
| withtime _messageTime

// ① 最後に記録したログイン時点のタイムスタンプ（末尾 ts: タイムスタンプ）
| most_recent(_messageTime_withtime) as last_login_ts by sourceuser 

// ② 現在時刻と①の差分。ついでにミリ秒 → 秒に整形（末尾 s: セカンズ）
| (now() / 1000) - (last_login_ts / 1000) as diff_login_ts_s 

// タイムスタンプが yyyy年MM月dd日 HH時mm分 になるようにデータフォーマット
| formatDate(fromMillis(last_login_ts), "yyyy年MM月dd日 HH時mm分", "JST") as 最終ログイン
| formatDate(now(), "yyyy年MM月dd日 HH時mm分", "JST") as 現在時刻 

// ③ 秒数を日数に変換。24時間 = 86400秒（末尾 d: day の d）
| diff_login_ts_s / 86400 as diff_login_ts_d 

// ④ 最終ログイン日と現在時刻との差分。③の結果を小数点第１位以下は繰り上げて切り捨て（末尾 r: round の r）
| round(diff_login_ts_d, 1) as diff_login_ts_d_r

// 90日以上ログインしていないユーザに絞り込み。数値(90)を変更すれば検出する期間を変更できます
| where diff_login_ts_d_r >= 90

// ⑤ ④の結果に文字列「日」を付与
| concat(diff_login_ts_d_r, "日") as %"非ログイン日数 | 日" 

| sourceuser as ユーザ

// 表示するフィールドを指定
| fields ユーザ, 最終ログイン, 現在時刻, %"非ログイン日数 | 日"

// 並べ替え  
| sort by %"非ログイン日数 | 日"

一点だけ注意点ですが、検索する時間範囲を 90日以上にしてください。以下画像の赤枠の部分をダブルクリックして -180d とすると、過去180日のデータを対象に検索できます。（数値は任意の数を入れることができます。）

あとは、ご自由に編集して使っていただいて構いません。特に最後のほうの fields や、sort 演算子など。

その他の解説や補足、注意点

• fields には、演算子の後ろに表示させたいフィールドを入れられます。フィールドを入れ替えることで、テーブルの順序を並べ替えることもできます。
• sort は デフォルト降順。sort by フィールド名 asc で昇順にできます。
• round 演算子は、round(フィールド名, 数字) と書きます。数字の部分を 2 にすると小数点第２位までを表示します。0 にしてしまうと、少数点第１位が .5 ~ 9 の時に繰り上がってしまうので、0 にはしないでください。
• フィールド名（%"非ログイン日数 | 日" ）の書き方は、空白やパイプなどの特殊文字を入れる場合に %"フィールド名" とすることで、ダブルクォーテーション内をフィールド名にすることが出来ます。空白や特殊文字など含むフィールド名にするならこの形にしなければエラーが出ます。集計結果として見るときの可読性を高めるために %"" を使ってフィールド化しています。
• あとは、日本語フィールド名ですが、as 日本語 で普通に日本語のフィールド名を作成できます。

では、上記クエリで検索してみて結果が正常なのかを検証していきます。

クエリの検証

検証方法ですが、まずは where diff_login_ts_d_r >= 90 の部分を削除して非ログイン日数を確認してみます。

一応、実行したクエリを載せておきます。（非ログイン日数を抽出するだけのクエリ）

// sumologic_audit（監査ログ）に対して、login のアクションが success しているログを検索
_index=sumologic_audit action=login status=SUCCESS

// sumologic_audit の sourceCategory として user_activity のログにフィルタリング
| where _sourceCategory="user_activity"

// withtime <時間情報を持つフィールド> でミリ秒単位のタイムスタンプを含む JSON オブジェクトを作成
| withtime _messageTime

// ① 最後に記録したログイン時点のタイムスタンプ（末尾 ts: タイムスタンプ）
| most_recent(_messageTime_withtime) as last_login_ts by sourceuser 

// ② 現在時刻と①の差分。ついでにミリ秒 → 秒に整形（末尾 s: セカンズ）
| (now() / 1000) - (last_login_ts / 1000) as diff_login_ts_s 

// タイムスタンプが yyyy年MM月dd日 HH時mm分 になるようにデータフォーマット
| formatDate(fromMillis(last_login_ts), "yyyy年MM月dd日 HH時mm分", "JST") as 最終ログイン
| formatDate(now(), "yyyy年MM月dd日 HH時mm分", "JST") as 現在時刻 

// ③ 秒数を日数に変換。24時間 = 86400秒（末尾 d: day の d）
| diff_login_ts_s / 86400 as diff_login_ts_d 

// ④ 最終ログイン日と現在時刻との差分。③の結果を小数点第１位以下は繰り上げて切り捨て（末尾 r: round の r）
| round(diff_login_ts_d, 1) as diff_login_ts_d_r

// ⑤ ④の結果に文字列「日」を付与
| concat(diff_login_ts_d_r, "日") as %"非ログイン日数 | 日" 

| sourceuser as ユーザ

// 表示するフィールドを指定
| fields ユーザ, 最終ログイン, 現在時刻, %"非ログイン日数 | 日"

// 並べ替え  
| sort by %"非ログイン日数 | 日"

そして、結果が以下です。

ここで、１日以上ログインしていないユーザが３人いることを覚えておいてください。

弊社環境では、90日間ログインしていないユーザがいなかったので、where diff_login_ts_d_r >= 90 の部分を where diff_login_ts_d_r >= 1 に変更して 1日以上ログインしていないユーザを抽出してみます。

念のため、実行したクエリを載せておきます。（１日以上ログインしてないユーザを抽出するクエリ）

// sumologic_audit（監査ログ）に対して、login のアクションが success しているログを検索
_index=sumologic_audit action=login status=SUCCESS

// sumologic_audit の sourceCategory として user_activity のログにフィルタリング
| where _sourceCategory="user_activity"

// withtime <時間情報を持つフィールド> でミリ秒単位のタイムスタンプを含む JSON オブジェクトを作成
| withtime _messageTime

// ① 最後に記録したログイン時点のタイムスタンプ（末尾 ts: タイムスタンプ）
| most_recent(_messageTime_withtime) as last_login_ts by sourceuser 

// ② 現在時刻と①の差分。ついでにミリ秒 → 秒に整形（末尾 s: セカンズ）
| (now() / 1000) - (last_login_ts / 1000) as diff_login_ts_s 

// タイムスタンプが yyyy年MM月dd日 HH時mm分 になるようにデータフォーマット
| formatDate(fromMillis(last_login_ts), "yyyy年MM月dd日 HH時mm分", "JST") as 最終ログイン
| formatDate(now(), "yyyy年MM月dd日 HH時mm分", "JST") as 現在時刻 

// ③ 秒数を日数に変換。24時間 = 86400秒（末尾 d: day の d）
| diff_login_ts_s / 86400 as diff_login_ts_d 

// ④ 最終ログイン日と現在時刻との差分。③の結果を小数点第１位以下は繰り上げて切り捨て（末尾 r: round の r）
| round(diff_login_ts_d, 1) as diff_login_ts_d_r

// 1日以上ログインしていないユーザに絞り込み
| where diff_login_ts_d_r >= 1

// ⑤ ④の結果に文字列「日」を付与
| concat(diff_login_ts_d_r, "日") as %"非ログイン日数 | 日" 

| sourceuser as ユーザ

// 表示するフィールドを指定
| fields ユーザ, 最終ログイン, 現在時刻, %"非ログイン日数 | 日"

// 並べ替え  
| sort by %"非ログイン日数 | 日"

以下が結果です。

想定していた通り、1日以上ログインしていないユーザだけを抽出できました。検証はこれで終了です。

アラート設定

画像でご説明します。

検索画面で Save as を選択して Scheduled Search 機能でアラートを設定します。

１日ごとにスケジューリングする設定「Run frequency: Daoly」「Every: Day」「Time of Day: 任意の時間」

Time range for scheduled search は、クエリ検出条件が | where diff_login_ts_d_r >= 90 なら、90日以上にしてください。例えば、100日なら、差分の10日間だけクエリで検出できます。そのため、90日以上で長めにご設定ください。

あと気を付けていただきたいのは、アラート発報させる条件の部分ですね。Send Notification: if the following condition is met にして、Number of results: 1 にすることで、１件以上の非ログイン日数 90日以上のユーザを検出した時にアラート発報できます。

Recipients: に通知先メールアドレスをコンマ区切りで入力します。（検出したユーザ宛に飛ばすことは出来ません。ここで事前に設定した特定のユーザにのみ通知できます。） Include in email: の項目で、 ResultSet はオンにしておくとメール内でどのユーザが検出されたのかをテーブルで確認できます。

後は任意でご設定いただき、保存して完了です。

まとめ

いかがでしたでしょうか？クエリは少し複雑かもしれませんが、時間を計算して特定の期間ログインしていないユーザを抽出することが出来ました。 本ブログのクエリを参考に他のユースケースにも繋がれば嬉しく思います。

皆様の一助になれば幸いです。

参考元（Sumo Logic ドキュメントページ）：

• most_recent, least_recent Grouping Operators
• Log Operators Cheat Sheet
• formatDate Search Operator